<?php

namespace App\Api;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;

//首页数据统计
class HomeController extends Controller
{
    //咨询、诉求、举报数量统计模块

    public function statistic(Request $request)
    {

        //1、传递参数部分
        $time = Carbon::now();
        $year = $request->year ? $request->year : $time->year;
        $month = $request->month ? $request->month : null;
        $day = $request->day ? $request->day : null;
        $state = $request->state ? $request->state : false;
//        2、按月统计1-12月
        $consultations = DB::table("consultations")
            ->when($year, function ($query) use ($year, $month, $day) {
                $query->whereYear('created_at', $year);
            })
            ->when($month, function ($query) use ($month) {
                $query->whereMonth('created_at', '=', $month);
            })
            ->when($day, function ($query) use ($day) {
                $query->whereDay('created_at', '=', $day);
            })
            ->select([DB::raw('date_format(created_at,"%Y-%m") as month'), DB::raw('"咨询" as name'), DB::raw('COUNT(*) as total')])
            ->groupByRaw("month")->orderBy('month', 'asc')
            ->get();

        $appeals = DB::table("appeals")
            ->when($year, function ($query) use ($year, $month, $day) {
                $query->whereYear('created_at', $year);
            })
            ->when($month, function ($query) use ($month) {
                $query->whereMonth('created_at', '=', $month);
            })
            ->when($day, function ($query) use ($day) {
                $query->whereDay('created_at', '=', $day);
            })
            ->select([DB::raw('date_format(created_at,"%Y-%m") as month'), DB::raw('"诉求" as name'), DB::raw('COUNT(*) as total')])
            ->groupByRaw("month")->orderBy('month', 'asc')
            ->get();
        $reports = DB::table("reports")
            ->when($year, function ($query) use ($year, $month, $day) {
                $query->whereYear('created_at', $year);
            })
            ->when($month, function ($query) use ($month) {
                $query->whereMonth('created_at', '=', $month);
            })
            ->when($day, function ($query) use ($day) {
                $query->whereDay('created_at', '=', $day);
            })
            ->select([DB::raw(DB::raw('date_format(created_at,"%Y-%m") as month')), DB::raw('"举报" as name'), DB::raw('COUNT(*) as total ')])
            ->groupByRaw("month")->orderBy('month', 'asc')
            ->get();
        return collect($consultations)->merge($appeals)->merge($reports);
    }

}
